package whf.framework.tools.data.imp;

import java.io.File;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;

/**
 * @author king
 *
 */
public class ImportFromXls {
	private final static SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd");
	private final static SimpleDateFormat SDF2 = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
	
	private Properties props = null;
	
	private String getString(String key) throws Exception {
		if(props == null ) {
			props = new Properties();
			InputStream is = ImportFromXls.class.getResourceAsStream("config.properties");
			props.load(is);
			is.close();
		}
		return props.getProperty(key);
	}
	
	private Connection getConnection() throws Exception{
		String driverClassName = this.getString("driverClassName");
		String url = this.getString("url");
		String username = this.getString("username");
		String password = this.getString("password");
		Class.forName(driverClassName);
		return DriverManager.getConnection(url, username, password);
	}
	
	private long convert2Date(String input) {
		if(input == null) return 0;
		try{
			if(input.length() <= 10) {
				return SDF.parse(input).getTime();
			} else {
				return SDF2.parse(input).getTime();
			}
		} catch (Exception e){
			return 0;
		}
	}
	
	private double convert2Number(String input) {
		try{
			return Double.parseDouble(input);
		} catch (Exception e){
			return 0;
		}
	}
	
	public void importFromXls() throws Exception {
		String fromFile = getString("fromFile");
		
		String toTable = getString("toTable");
		String toFields[] = getString("toFields").split(",");
		int columnCount = toFields.length;
		Connection con = null;
		try{
			con = this.getConnection();
			String sql = "insert into " + toTable + "(" + toFields[0];
			for(int i=1; i< toFields.length; i++ ){
				sql += "," + toFields[i];
			}
			sql += ") values(?";
			for(int i=1; i< toFields.length; i++) {
				sql += ",?";
			}
			sql += ")";
			PreparedStatement pstmt = con.prepareStatement(sql);
			Workbook workbook = Workbook.getWorkbook(new File(fromFile));
			Sheet sheet = workbook.getSheet(0);
			for(int r=1; r < sheet.getRows(); r++) {
				for(int c = 0; c < columnCount; c++){
					Cell cell = sheet.getCell(c, r);
					String value = cell == null? null: cell.getContents();
					if(cell == null || value == null) {
						pstmt.setString(c + 1, null);
						continue;
					}
					CellType type = cell.getType();
					if(type == CellType.DATE) {
						long l = this.convert2Date(value);
						if(l == 0) {
							pstmt.setDate(c + 1, null);
						} else if(value.length() <= 10) {
							pstmt.setDate(c + 1, new Date(l));
						} else {
							pstmt.setTimestamp(c + 1, new Timestamp(l));
						}
					} else if(type == CellType.NUMBER) {
						pstmt.setDouble(c + 1, this.convert2Number(value));
					} else {
						pstmt.setString(c + 1, value);
					}
				}
				pstmt.executeUpdate();
			}
		} finally {
			if(con != null)
				con.close();
		}
	}
	
	public static void main(String[] args) throws Exception {
		ImportFromXls i = new ImportFromXls();
		i.importFromXls();
	}
}
